package com.jiepos.mpos.biz.operationlog.service;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.RandInfDao;
import com.jiepos.mpos.common.entity.App_Rand_InfEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.CollectionUtil;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.ExcelUtil;
import com.jiepos.mpos.core.util.IDFactoryUtil;
import com.jiepos.mpos.core.util.StringUtils;

@Service
public class SmsCodeFlowService extends BaseService {

	/** 导出excel文件表头*/
	private static List<String> excelHead = null;
	/** sheet页名称*/
	private static String EXCEL_SMSCODE_NAME = "短信验证码流水";
	@Autowired
	private RandInfDao randInfDao;
	
	/**
	 * 获取短信验证码流水Json
	 * @param pagination
	 * @param request
	 * @param app_Rand_InfEntity
	 * @return
	 */
	public Map getSmsCodeFlowList(Pagination pagination, HttpServletRequest request, App_Rand_InfEntity app_Rand_InfEntity){
		JSONObject json = getQueryRandSql(app_Rand_InfEntity);
		pagination.setQueryString(json.getString("sql"));
		pagination = this.getOrderSql(getInsAuthSql(request, pagination, "RI", null), "RI");
		return randInfDao.getPaginationMap(pagination, "t1", json.getObject("params", List.class));
	}


	/**
	 * 不再使用，后期删除
	 * @param service_type
	 * @param phone
	 * @param login_id
	 * @return
	 */
	@Deprecated
	public String getRandStr(String service_type,String phone,String login_id){
		//验证码校验
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("   select t1.rand ")
				.append("  from t_app_rand_inf t1 ")
				.append("  where t1.send_dt || t1.send_ts = ")
				.append("  to_char(sysdate, 'yyyyMMddHH24miss') - ")
				.append("  to_char(to_date(t1.send_dt || t1.send_ts, 'yyyyMMddHH24miss'), ")
				.append("  'yyyyMMddHH24miss') ")
				.append("  and t1.service_type = ? and t1.rec_st = 1 and t1.phone = ? order by t1.rec_id desc");
		List listParam = new ArrayList();
		listParam.add(service_type);
		listParam.add(login_id);
		List<Map<String, Object>> listPhone = randInfDao.queryForList(sqlBuf.toString(), listParam);
		if(CollectionUtil.isEmpty(listPhone)){
			return null;
		}else{
			Map randMap= listPhone.get(0);
			return String.valueOf(randMap.get("rand"));
		}
	}

	/**
	 * 得到60秒内的该手机号该业务的最新一条短信验证码的发送时间
	 * @param service_type 业务类型
	 * @param phone 手机号 login_id
	 * @return true：匹配 ； false：不匹配
	 */
	public Date getLastRandSendTime(String service_type,String phone){
		StringBuffer sqlBuf = new StringBuffer();
		//系统时间
		String dateTimeStr = DateUtil.getCurrDateTimeStr();
		sqlBuf.append(" SELECT A.SEND_TIME ")
				.append("   FROM (SELECT TO_DATE(SEND_DT || SEND_TS, 'yyyyMMddHH24miss') SEND_TIME ")
				.append("           FROM  "+Table.T_APP_RAND_INF)
				.append("          WHERE TO_DATE(SEND_DT || SEND_TS, 'yyyyMMddHH24miss') BETWEEN ")
				.append("                TO_DATE('"+dateTimeStr+"', 'yyyyMMddHH24miss') - 60 / (24 * 60 * 60) AND ")
				.append("                TO_DATE('"+dateTimeStr+"', 'yyyyMMddHH24miss') ")
				.append("            AND REC_ST != '0' ")
				.append("            AND RAND_ST != '0' ")
				.append("            AND SERVICE_TYPE = ? ")
				.append("            AND PHONE = ? ")
				.append("          ORDER BY REC_CRT_TS DESC) A ")
				.append("  WHERE ROWNUM <= 1 ");
		List listParam = new ArrayList();
		listParam.add(service_type);
		listParam.add(phone);
		List<Map<String, Object>> list = randInfDao.queryForList(sqlBuf.toString(), listParam);
		if(list != null && list.size() > 0 && list.get(0) !=null && list.get(0).get("SEND_TIME") !=null){
			return (Date) list.get(0).get("SEND_TIME");
		}else{
			return null;
		}
	}

	/**
	 * 校验验证码
	 * @param service_type 业务类型
	 * @param phone 手机号 login_id
	 * @param rand 待校验的验证码
	 * @return true：匹配 ； false：不匹配
	 */
	public boolean checkRand(String service_type,String phone,String rand){
		StringBuffer sqlBuf = new StringBuffer();
		//系统时间
		String dateTimeStr = DateUtil.getCurrDateTimeStr();
		sqlBuf.append(" SELECT A.RAND ")
				.append("   FROM (SELECT RAND ")
				.append("           FROM  "+Table.T_APP_RAND_INF)
				.append("          WHERE TO_DATE(SEND_DT || SEND_TS, 'yyyyMMddHH24miss') BETWEEN ")
				.append("                TO_DATE('"+dateTimeStr+"', 'yyyyMMddHH24miss') - 5 / (24 * 60) AND ")
				.append("                TO_DATE('"+dateTimeStr+"', 'yyyyMMddHH24miss') ")
				.append("            AND REC_ST != '0' ")
				.append("            AND RAND_ST != '0' ")
				.append("            AND SERVICE_TYPE = ? ")
				.append("            AND PHONE = ? ")
				.append("          ORDER BY REC_CRT_TS DESC) A ")
				.append("  WHERE ROWNUM <= 1 ");
		List listParam = new ArrayList();
		listParam.add(service_type);
		listParam.add(phone);
		List<Map<String, Object>> list = randInfDao.queryForList(sqlBuf.toString(), listParam);
		if(list != null && list.size() > 0 && list.get(0) !=null && list.get(0).get("RAND") !=null){
			String preRand = String.valueOf(list.get(0).get("RAND"));
			//比较两个验证码是否一致
			return StringUtils.compare(rand,preRand);
		}else{
			return false;
		}
	}
	/**
	 * 使用过后验证码更新rec_st为无效状态
	 * @param service_type
	 * @param phone
	 * @param rand
	 */
	public void updRand(String service_type,String phone,String rand){
		StringBuffer sql = new StringBuffer();
		sql.append(" update " + Table.T_APP_RAND_INF + " set ")
				.append("  rand_st = ? ,  ")
				.append("  rec_upd_ts =? ")
				.append("  where phone = ? AND SERVICE_TYPE = ?  ");
		List param = new ArrayList();
		param.add(SystemConstant.RAND_ST_0);
		param.add(new Timestamp(System.currentTimeMillis()));
		param.add(phone);
		param.add(service_type);
		randInfDao.update(sql.toString(),param);
	}

	/**
	 * 保存验证码发送流水
	 * @param random 验证码
	 * @param type 业务类型
	 * @param reqCode 响应码
	 * @param reqMsg 响应消息体
	 * @param ins_uuid 机构uuid
	 * @param login_id 登录名/手机号
	 * @return
	 */
	public String saveSmsCodeFlow(String random,String type,String reqCode,String reqMsg,String ins_uuid,String login_id){
		App_Rand_InfEntity entity = new App_Rand_InfEntity();
		entity.setRand(random);
		entity.setService_type(type);
		entity.setSend_dt(DateUtil.getCurrDateStr());
		entity.setSend_ts(DateUtil.getCurrTimeStr());
		entity.setPhone(login_id);
		if("00".equals(reqCode)){
			//有效
			entity.setRand_st("1");
		}else{
			//无效
			entity.setRand_st("0");
		}
		entity.setRsp_cd(reqCode);
		entity.setRsp_msg(reqMsg);
		entity.setIns_uuid(ins_uuid);
		//新增
		entity.setRec_crt_ts(new Timestamp(System.currentTimeMillis()));
		entity.setLast_oper_in(SystemConstant.OPER_ADD);
		entity.setRec_upd_ts(new Timestamp(System.currentTimeMillis()));
		entity.setRec_st(SystemConstant.NORMAL_STATUS);
		entity.setRec_upd_usr_id(login_id);
		entity.setRand_uuid(IDFactoryUtil.getUUID());
		randInfDao.save(entity);
		return SystemConstant.SUCCESS;
	}
	/**
	 * 校验验证码30s以内(分享注册页面校验使用)
	 * @param service_type 业务类型
	 * @param phone 手机号 login_id
	 * @return true：匹配 ； false：不匹配
	 */
	public boolean ifExistRand(String service_type,String phone){
		StringBuffer sqlBuf = new StringBuffer();
		//系统时间
		String dateTimeStr = DateUtil.getCurrDateTimeStr();
		sqlBuf.append(" SELECT A.RAND ")
				.append("   FROM (SELECT RAND ")
				.append("           FROM  "+Table.T_APP_RAND_INF)
				.append("          WHERE TO_DATE(SEND_DT || SEND_TS, 'yyyyMMddHH24miss') BETWEEN ")
				.append("                TO_DATE('"+dateTimeStr+"', 'yyyyMMddHH24miss') - 30 / (24 * 60 * 60) AND ")
				.append("                TO_DATE('"+dateTimeStr+"', 'yyyyMMddHH24miss') ")
				.append("            AND REC_ST != '0' ")
				.append("            AND SERVICE_TYPE = ? ")
				.append("            AND PHONE = ? ")
				.append("          ORDER BY REC_CRT_TS DESC) A ")
				.append("  WHERE ROWNUM <= 1 ");
		List listParam = new ArrayList();
		listParam.add(service_type);
		listParam.add(phone);
		List<Map<String, Object>> list = randInfDao.queryForList(sqlBuf.toString(), listParam);
		if(list != null && list.size() > 0 && list.get(0) !=null && list.get(0).get("RAND") !=null){
			return false;
		}else{
			return true;
		}
	}

	/**
	 * 导出
	 * @param appRandInfEntity
	 * @param fileName
	 * @return
	 */
    public String exportSmsCodeFlowList(HttpServletRequest request, String fileName, App_Rand_InfEntity appRandInfEntity) {
    	JSONObject json = getQueryRandSql(appRandInfEntity);
		List<Map> dataList = randInfDao.queryForList(getInsAuthSql(request, json.getString("sql"), "RI", null), json.getObject("params", List.class));
		return ExcelUtil.writeExcel(fileName, EXCEL_SMSCODE_NAME, excelHead, dataList);
    }

	/**
	 * 组装sql
	 * @param app_Rand_InfEntity
	 * @return
	 */
	private JSONObject getQueryRandSql(App_Rand_InfEntity app_Rand_InfEntity){
		StringBuffer sql = new StringBuffer();
		List<String> list = new ArrayList<>();
		sql.append("SELECT RI.RAND_UUID,")
		.append("       TO_CHAR(TO_DATE(RI.SEND_DT || RI.SEND_TS, 'YYYYMMDDHH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') SEND_TIME,")
		.append("       SUBSTR(RI.PHONE, 1, 3) || '****' || SUBSTR(RI.PHONE, 8) PHONE,")
		.append("       SUBSTR(RI.RAND, 1, 2) || '**' || SUBSTR(RI.RAND, 5) RAND,")
		.append("       RT.CODE_VAL || '-' || RT.CODE_DESC SERVICE_TYPE,")
		.append("       II.INS_CD || '-' || II.INS_CN_NM INS_NAME,")
		.append("       RS.CODE_VAL || '-' || RS.CODE_DESC RAND_ST,")
		.append("       RI.RSP_CD || '-' || RI.RSP_MSG RSP_CD")
		.append("  FROM T_APP_RAND_INF RI")
		.append("  LEFT JOIN T_APP_CODE_LIST RT")
		.append("    ON RI.SERVICE_TYPE = RT.CODE_VAL")
		.append("   AND RT.CODE_TP = 'RAND_TYPE'")
		.append("  LEFT JOIN T_APP_CODE_LIST RS")
		.append("    ON RI.RAND_ST = RS.CODE_VAL")
		.append("   AND RS.CODE_TP = 'RAND_ST'")
		.append("  LEFT JOIN T_APP_INS_INF II")
		.append("    ON RI.INS_UUID = II.INS_UUID")
		.append(" WHERE 1 = 1");
		/*验证起始日期*/
		if(!StringUtils.isBlank(app_Rand_InfEntity.getSend_dt())){
			sql.append(" AND RI.SEND_DT >= ?");
			list.add(app_Rand_InfEntity.getSend_dt().replaceAll("-", ""));
		}
		/*验证结束日期*/
		if(!StringUtils.isBlank(app_Rand_InfEntity.getSend_ts())){
			sql.append(" AND RI.SEND_DT <= ?");
			list.add(app_Rand_InfEntity.getSend_ts().replaceAll("-", ""));
		}
		if(!StringUtils.isBlank(app_Rand_InfEntity.getRand_st())){
			sql.append(" AND RI.RAND_ST = ?");
			list.add(app_Rand_InfEntity.getRand_st());
		}
		if(!StringUtils.isBlank(app_Rand_InfEntity.getPhone())){
			sql.append(" AND RI.PHONE LIKE ?");
			list.add("%"+app_Rand_InfEntity.getPhone()+"%");
		}
		if(!StringUtils.isBlank(app_Rand_InfEntity.getService_type())){
			sql.append(" AND RI.SERVICE_TYPE = ?");
			list.add(app_Rand_InfEntity.getService_type());
		}
		if(!StringUtils.isBlank(app_Rand_InfEntity.getIns_uuid())){
			sql.append(" AND RI.INS_UUID = ?");
			list.add(app_Rand_InfEntity.getIns_uuid());
		}
		if(!StringUtils.isBlank(app_Rand_InfEntity.getRec_st())){
			sql.append(" AND RI.REC_ST = ?");
			list.add(app_Rand_InfEntity.getRec_st());
		}
		JSONObject json = new JSONObject();
		json.put("sql", sql.toString());
		json.put("params", list);
		return json;
	}

	@PostConstruct
	private void init(){
		excelHead = new ArrayList<>();
		excelHead.add("发送时间");
		excelHead.add("接收方电话");
		excelHead.add("验证码");
		excelHead.add("业务类型");
		excelHead.add("所属机构");
		excelHead.add("状态");
		excelHead.add("发送响应码-内容");
	}
}
